<?php 
class oracle{
	const RESULT_FIELD_NAME_LOWER = false; 
    const NLS_DATE_FORMAT = 'yyyy-mm-dd hh24:mi:ss';
	const NEXT_ID_SQL    = 'SELECT (%s.nextval) NEWID FROM DUAL';
	const CREATE_SEQ_SQL = 'CREATE SEQUENCE %s START WITH 1 NOCACHE';

	var $conn = false;
	
	function __construct($conninfo){
		$charset = $conninfo['charset']?$conninfo['charset']:'';
		$db = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=".$conninfo['host'].")(PORT=1521)))(CONNECT_DATA=(SID=".$conninfo['dbname'].")))";
		$this->conn = oci_connect($conninfo['username'],$conninfo['password'],$db,$charset) or die('数据库存连接失败！');	
		$this->execute('ALTER SESSION SET NLS_DATE_FORMAT = \''.oracle::NLS_DATE_FORMAT.'\'');
	}

	function execute($sql){
		$stmt = &$this->_execute($sql);
		return $stmt?oci_num_rows($stmt):-1;
	}

	function query($sql, $page = 0, $pageSize = 10,&$totalCount=0){
		//不分页
		if($page==0){
			$stmt = &$this->_execute($sql);
			$data = array();
			while($row=$this->fetch($stmt)){
				$data[]= $row;
			}
			$totalCount = count($row);
			return $data;
		}
		
		$sqlCount = "select count(1) cnt  from (".$this->stripOrderBy($sql).") t";
		$totalCount = $this->query($sqlCount);	
		$totalCount = $totalCount[0]['CNT'];
		
		//第一页
		if($page==1){
			$sql = "select t.* from ({$sql}) t where rownum<=".$pageSize;
		}elseif($page>1){// 其他分页			

			$limit = array($pageSize,($page-1)*$pageSize);
			
			$offset = ($page-1)*$pageSize+1;
			$length = $pageSize+$offset-1; 
			
			$sql = "SELECT tt.* FROM " .
                   "(SELECT rownum as jyapi_db_rownum, t.* FROM " .
                   "({$sql}) t" .
                   ' WHERE rownum <= '.$length.') tt WHERE jyapi_db_rownum >= '.$offset;
		}
		
		$stmt = &$this->_execute($sql);
		$data = array();
		while($row=$this->fetch($stmt)){
			unset($row['JYAPI_DB_ROWNUM']);
			$data[]= $row;
		}
		return $data;
	}

	function find($table,$where,$fields='*',$order=''){

		$row = $this->findAll($table,$where,$fields,$order,1,1);
		return $row[0];
	}

	function findAll($table,$where='',$fields='*',$order='', $page = 0, $pageSize = 10,&$totalCount=0){
		$sql = "select {$fields}  from ".$table." where 1=1 "
			.($where?" and ".$where:"")
			.($order?" order by ".$order:"");

		return $this->query($sql,$page,$pageSize,&$totalCount);
	}

	function update($table,$where,$row){
		if(is_array($row)){
			$update = '';
			foreach($row as $f=>$v){
				$update .=",".$f."=:".$f;
			}
			$update = trim($update,',');
		}

		$sql = "UPDATE ".$table." SET ".$update." WHERE ".$where;

		$stmt = &$this->_execute($sql,$row);
		//echo $sql;
		return $stmt?oci_num_rows($stmt):-1;
	}

	function create($table,$row,$pkField=''){
		if($pkField&&!$row[$pkField]){
			$row[$pkField] = $this->getNewId($table); 
		}
	
		if(is_array($row)){
			$fields = '';
			$value = '';
			foreach($row as $f=>$v){
				$fields .=",".$f;
				$value .=",:".$f;
			}
			$fields = trim($fields,',');
			$value = trim($value,',');
		}
		
		$sql = "INSERT INTO ".$table."(".$fields.") VALUES(".$value.")";
		
		$stmt = &$this->_execute($sql,$row);
		//当传入主键时，成功默认返回1
		$nowid = $row[$pkField]?$row[$pkField]:1;
		return $stmt?$nowid:-1;
	}
	
	function getNewId($table){
		$createSql = sprintf(oracle::CREATE_SEQ_SQL,$table.'_SEQ');
		$findSql = sprintf(oracle::NEXT_ID_SQL,$table.'_SEQ');
		$newid = $this->query($findSql);
				
		if($newid) return $newid[0]['NEWID']; 
		$this->execute($createSql);

		$newid = $this->query($findSql);
		
		if($newid) return $newid[0]['NEWID']; 
	}

	function del($table,$where){
		$conditions = null;
		if(is_array($where)){
			$conditions = $where;
			$where = '';
			foreach($conditions as $f=>$v){
				$where .=",".$f."=:".$f;
			}
			$where = trim($where,',');
		}
		$sql = "DELETE FROM ".$table." WHERE ".$where;
		$stmt = &$this->_execute($sql,$conditions);
		return $stmt?oci_num_rows($stmt):-1;
	}
	
	/*执行SQL
	参数:
	@params $sql string
	@params $row array 
	*/
	private function &_execute($sql,$row=null){
		//JYTools::error("执行SQL：".$sql."\n\t数据：".print_r($row,true));
		
		$stmt = oci_parse($this->conn,$sql);
	
		//bind 数据 
		if(is_array($row)){
			foreach($row as $k=>$v){
				oci_bind_by_name($stmt, ":{$k}", $row[$k]);
			}
		}
		$rs = oci_execute($stmt);
		if(!$rs){
			$e = oci_error($stmt);
		}

		return $stmt;
	}

	/*取下一行数据
	返回:成功，返回结果数组
	*/
	private function fetch(&$stmt){
		$row = array();
		$err = @ocifetchinto($stmt, $row, OCI_ASSOC | OCI_RETURN_LOBS | OCI_RETURN_NULLS);
		if(!$err){
			$e = oci_error($stmt);
		}
        if (oracle::RESULT_FIELD_NAME_LOWER) {
            $row = array_change_key_case($row, CASE_LOWER);
        }
		return $row;
	}

	private function getFields($table){
		if($this->conn) return false;
	
		$sql = "SELECT CNAME FROM COL WHERE UPPER(TNAME)='".strtoupper($table)."' ORDER BY COLNO";

		$rs = $this->execute($sql);
		if($rs<=0) return false;

		$fields = array();
		while($row = $this->fetch($stmt)){
			$fields[]= oracle::RESULT_FIELD_NAME_LOWER?strtolower($row['CNAME']):$row['CNAME'];
		}
		return $fields;
	}
	
	private function stripOrderBy($sql){
		$rez = preg_match('/(\sORDER\s+BY\s[^)]*)/is',$sql,$arr);
		if ($arr)
			if (strpos($arr[0],'(') !== false) {
				$at = strpos($sql,$arr[0]);
				$cntin = 0;
				for ($i=$at, $max=strlen($sql); $i < $max; $i++) {
					$ch = $sql[$i];
					if ($ch == '(') {
						$cntin += 1;
					} elseif($ch == ')') {
						$cntin -= 1;
						if ($cntin < 0) {
							break;
						}
					}
				}
				$sql = substr($sql,0,$at).substr($sql,$i);
			} else
				$sql = str_replace($arr[0], '', $sql); 
		return $sql;
	 }

	function close(){
		if ($this->conn) {
			ocilogoff($this->conn); 
		}
	}

	function __destruct(){
		$this->close();
	} 
}